package com.example.blogapi.dao;

import com.example.blogapi.model.Tag;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

public class TagDao {

    public List<Tag> findAll() throws SQLException {
        String sql = "SELECT * FROM tags";
        List<Tag> tags = new ArrayList<>();

        try (Connection conn = DatabaseManager.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                Tag tag = mapResultSetToTag(rs);
                tags.add(tag);
            }
        }

        return tags;
    }

    public Tag findById(Long id) throws SQLException {
        String sql = "SELECT * FROM tags WHERE id = ?";

        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setLong(1, id);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return mapResultSetToTag(rs);
                }
            }
        }

        return null;
    }

    public Tag findByName(String name) throws SQLException {
        String sql = "SELECT * FROM tags WHERE name = ?";

        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, name);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return mapResultSetToTag(rs);
                }
            }
        }

        return null;
    }

    public Tag create(Tag tag) throws SQLException {
        String sql = "INSERT INTO tags (name, created_at) VALUES (?, ?)";

        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            LocalDateTime now = LocalDateTime.now();
            tag.setCreatedAt(now);

            pstmt.setString(1, tag.getName());
            pstmt.setString(2, now.toString());

            int affectedRows = pstmt.executeUpdate();

            if (affectedRows == 0) {
                throw new SQLException("Creating tag failed, no rows affected.");
            }

            try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    tag.setId(generatedKeys.getLong(1));
                } else {
                    throw new SQLException("Creating tag failed, no ID obtained.");
                }
            }
        }

        return tag;
    }

    public Tag update(Tag tag) throws SQLException {
        String sql = "UPDATE tags SET name = ? WHERE id = ?";

        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, tag.getName());
            pstmt.setLong(2, tag.getId());

            int affectedRows = pstmt.executeUpdate();

            if (affectedRows == 0) {
                throw new SQLException("Updating tag failed, no rows affected.");
            }
        }

        return tag;
    }

    public boolean delete(Long id) throws SQLException {
        String sql = "DELETE FROM tags WHERE id = ?";

        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setLong(1, id);

            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        }
    }

    public List<Tag> findByBlogId(Long blogId) throws SQLException {
        String sql = "SELECT t.* FROM tags t " +
                "JOIN blog_tags bt ON t.id = bt.tag_id " +
                "WHERE bt.blog_id = ?";
        List<Tag> tags = new ArrayList<>();

        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setLong(1, blogId);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Tag tag = mapResultSetToTag(rs);
                    tags.add(tag);
                }
            }
        }

        return tags;
    }

    private Tag mapResultSetToTag(ResultSet rs) throws SQLException {
        Tag tag = new Tag();
        tag.setId(rs.getLong("id"));
        tag.setName(rs.getString("name"));
        tag.setCreatedAt(LocalDateTime.parse(rs.getString("created_at")));
        return tag;
    }
}